package merlin.dao;

import java.util.*;
import java.sql.*;

import merlin.beans.*;

public class MsalResearchersDAO implements java.io.Serializable{

	Connection con;

	public MsalResearchersDAO(Connection connection){
		con = connection;
	}

	public MsalResearchersBean login(String uid,String pwd)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("SELECT MR_USER_NAME,MR_PASSWORD,MR_ID_NUMBER FROM MSAL_RESEARCHERS WHERE MR_USER_NAME = ? AND MR_PASSWORD = ?");
		pstmt.setString(1,uid);
		pstmt.setString(2,pwd);
		ResultSet rs = pstmt.executeQuery();

		MsalResearchersBean bean = null;
		if(rs.next()){
			bean = new MsalResearchersBean();
			bean.setUserName(rs.getString("MR_USER_NAME"));
			bean.setPassword(rs.getString("MR_PASSWORD"));
			bean.setMr_id_number(rs.getInt("MR_ID_NUMBER"));
		}
		rs.close();
		pstmt.close();
		return bean;
	}

	public CustomerDetailsBean loginForFreeUpdates(String emailId)throws SQLException{
		String sql = "SELECT CUSTOMER_DETAILS_ID,REPORT_TITLES.REPORT_TITLES_ID AS REPORT_TITLES_ID, " +
						"REPORT_TITLE, EMAIL_ADDRESS, SUBSCRIPTION_END_DATE,ADDITIONAL_REPORT_NUMBERS, REPORT_TYPE, COMPANY_NUMBER, BRANDED_REPORT " +
						" FROM CUSTOMER_DETAILS , REPORT_TITLES  " +
						" WHERE EMAIL_ADDRESS = ? AND " +
						" CUSTOMER_DETAILS.REPORT_TITLES_ID = REPORT_TITLES.REPORT_TITLES_ID ";

		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1,emailId);
		ResultSet rs = pstmt.executeQuery();

		CustomerDetailsBean bean = null;
		if(rs.next()){
			bean = new CustomerDetailsBean();
			bean.setCustomerDetailsId(rs.getInt("CUSTOMER_DETAILS_ID"));
			bean.setReportTitlesId(rs.getInt("REPORT_TITLES_ID"));
			bean.setReportTitle(rs.getString("REPORT_TITLE"));
			bean.setEmailAddress(rs.getString("EMAIL_ADDRESS"));
			bean.setSubscriptionEndDate(rs.getDate("SUBSCRIPTION_END_DATE"));
			bean.setAdditionalReportNumbers(rs.getString("ADDITIONAL_REPORT_NUMBERS"));
			bean.setReportType(rs.getInt("REPORT_TYPE"));
			bean.setCompanyNumber(rs.getString("COMPANY_NUMBER"));
			bean.setBrandedReport(rs.getString("BRANDED_REPORT"));
		}
		rs.close();
		pstmt.close();
		return bean;
	}
	
	public CustomerDetailsBean loginForBibService(String emailId, String password)throws SQLException{
		String sql = "SELECT CUSTOMER_DETAILS_ID,REPORT_TITLES.REPORT_TITLES_ID AS REPORT_TITLES_ID, " +
						"REPORT_TITLE, EMAIL_ADDRESS, SUBSCRIPTION_END_DATE,ADDITIONAL_REPORT_NUMBERS, " + 
						"REPORT_TYPE, COMPANY_NUMBER, BRANDED_REPORT, DOWNLOAD_PAGE_URL " +
						" FROM CUSTOMER_DETAILS , REPORT_TITLES  " +
						" WHERE EMAIL_ADDRESS = ? AND PASSWORD = ? " +
						" AND CUSTOMER_DETAILS.REPORT_TITLES_ID = REPORT_TITLES.REPORT_TITLES_ID ";

		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1,emailId);
		pstmt.setString(2, password);
		ResultSet rs = pstmt.executeQuery();

		CustomerDetailsBean bean = null;
		if(rs.next()){
			bean = new CustomerDetailsBean();
			bean.setCustomerDetailsId(rs.getInt("CUSTOMER_DETAILS_ID"));
			bean.setReportTitlesId(rs.getInt("REPORT_TITLES_ID"));
			bean.setReportTitle(rs.getString("REPORT_TITLE"));
			bean.setEmailAddress(rs.getString("EMAIL_ADDRESS"));
			bean.setSubscriptionEndDate(rs.getDate("SUBSCRIPTION_END_DATE"));
			bean.setAdditionalReportNumbers(rs.getString("ADDITIONAL_REPORT_NUMBERS"));
			bean.setReportType(rs.getInt("REPORT_TYPE"));
			bean.setCompanyNumber(rs.getString("COMPANY_NUMBER"));
			bean.setBrandedReport(rs.getString("BRANDED_REPORT"));
			bean.setDownloadPageUrl(rs.getString("DOWNLOAD_PAGE_URL"));
		}
		rs.close();
		pstmt.close();
		return bean;
	}

	public MsalResearchersBean selectMsalResearchersByPK(MsalResearchersBean bean)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("SELECT MR_USER_NAME,MR_PASSWORD,MR_ID_NUMBER FROM MSAL_RESEARCHERS WHERE MR_USER_NAME = ? ");
		pstmt.setString(1,bean.getUserName());
		ResultSet rs = pstmt.executeQuery();
		MsalResearchersBean bn = null;
		if(rs.next()){
			bn = new MsalResearchersBean();
			bn.setUserName(rs.getString("MR_USER_NAME"));
			bn.setPassword(rs.getString("MR_PASSWORD"));
			bn.setMr_id_number(rs.getInt("MR_ID_NUMBER"));
		}
		rs.close();
		pstmt.close();
		return bn;
	}

	public String addCustomReportToCustomerDetails(int userId, int customReportId)throws SQLException{
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT ADDITIONAL_REPORT_NUMBERS FROM CUSTOMER_DETAILS WHERE CUSTOMER_DETAILS_ID = " + userId);
		rs.next();
		String reportIds = rs.getString(1);
		rs.close();

		if(reportIds == null){
			reportIds = "/" + customReportId;
		}else{
			if(reportIds.endsWith("/")){
				reportIds += String.valueOf(customReportId);
			}else{
				reportIds += "/" + customReportId;
			}
		}

		int result = stmt.executeUpdate("UPDATE CUSTOMER_DETAILS SET ADDITIONAL_REPORT_NUMBERS = '" + reportIds + "' WHERE CUSTOMER_DETAILS_ID = " + userId);

		stmt.close();

		return reportIds;
	}
	
	public String deleteCustomReportFromCustomerDetails(int userId, int customReportId)throws SQLException{
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT ADDITIONAL_REPORT_NUMBERS FROM CUSTOMER_DETAILS WHERE CUSTOMER_DETAILS_ID = " + userId);
		rs.next();
		String reportIds = rs.getString(1);
		rs.close();

		String newReportsString = "";
		
		if(reportIds == null){
			// nothing to do, although this situation is a bug.
		}else{
			String[] reports = reportIds.split("/");
			String reportToDelete = String.valueOf(customReportId);
			
			for(String s : reports){
				if(! s.equals(reportToDelete) && s.length() > 0){
					newReportsString += "/" + s;
				}
			}
		}

		stmt.executeUpdate("UPDATE CUSTOMER_DETAILS SET ADDITIONAL_REPORT_NUMBERS = '" + newReportsString + "' WHERE CUSTOMER_DETAILS_ID = " + userId);

		stmt.close();

		return newReportsString;
	}

	
}